﻿using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Reflection;

namespace BestTrader.Cms.NPOI.Extensions
{
    public static class ExcelExtensions
    {
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="title"></param>
        public static string ToExcel<T>(this ExcelFile<T> excel)
        {
            var basePath = Path.GetDirectoryName(AppContext.BaseDirectory);     // get application located directory
            var filePath = Path.Combine(basePath, excel.FileDownloadName);      // (在以上步骤2设置生成xml的路径)

            using (var fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
            {
                IWorkbook workbook = new XSSFWorkbook();
                ISheet sheet1 = workbook.CreateSheet("Sheet1");

                var rowIndex = 0;
                #region 1. 创建标题

                if (!string.IsNullOrEmpty(excel.Title))
                {
                    var lastCol = excel.Names.Count - (excel.IsSeq ? 0 : 1);
                    var style = workbook.CreateCellStyle(20, true, true);
                    sheet1.CreateTitle(excel.Title, lastCol, rowIndex, style);
                    rowIndex++;
                }

                #endregion

                #region 2. 创建内容标题

                var style2 = workbook.CreateCellStyle(10, true, isBgColor: true);
                sheet1.CreateSheetTitle(excel.Names, rowIndex, excel.IsSeq, style2);
                rowIndex++;

                #endregion

                #region 3. 创建内容
                var data = excel.Data.ListToDic<T>();
                for (int i = 0; i < data.Count; i++)
                {
                    sheet1.CreateSheetRow(data[i], rowIndex, i + 1, excel.IsSeq);
                    rowIndex++;
                }
                
                #endregion

                workbook.Write(fs);
            }

            return filePath;
        }

        /// <summary>
        /// 集合转字典
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <returns></returns>
        private static List<Dictionary<string, string>> ListToDic<T>(this IEnumerable<T> list)
        {
            var result = new List<Dictionary<string, string>>();
            #region 2. 反射获取对象属性

            foreach (T item in list)
            {
                var data = new Dictionary<string, string>();
                Type t = typeof(T);
                // 获取所有属性
                // MemberInfo[] minfos = t.GetMembers();
                // “包含非公开”、“包含实例成员”和“包含公开” “不关注父类”
                // MemberInfo[] minfos = t.GetMembers(BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Public | BindingFlags.DeclaredOnly | BindingFlags.Static);
                FieldInfo[] finfos = t.GetFields(BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly);
                foreach (FieldInfo finfo in finfos)
                {
                    string name = finfo.Name;
                    string value = finfo.GetValue(item).ToString();
                    data.Add(name, value);
                }
                result.Add(data);
            }

            #endregion
            return result;
        }

        /// <summary>
        /// 创建单元格样式
        /// </summary>
        /// <param name="workbook"></param>
        /// <returns></returns>
        private static ICellStyle CreateCellStyle(this IWorkbook workbook, short fontSize = 12, bool isBold = false, bool isCenter = false, bool isBgColor = false)
        {
            var style2 = workbook.CreateCellStyle();
            if (isBgColor)
            {
                style2.FillForegroundColor = HSSFColor.LightOrange.Index;
                style2.FillPattern = FillPattern.SolidForeground;
            }
            IFont iFont = workbook.CreateFont();
            iFont.Color = HSSFColor.Black.Index;//设置颜色
            iFont.FontHeightInPoints = fontSize;
            if (isBold)
            {
                iFont.IsBold = true;
                //iFont.Underline = FontUnderlineType.Single;//设置链接下划线 
            }
            style2.SetFont(iFont);

            if (isCenter) style2.Alignment = HorizontalAlignment.Center;//【Center】居中 

            return style2;
        }

        /// <summary>
        /// 创建标题
        /// </summary>
        /// <param name="sheet1"></param>
        /// <param name="colNames"></param>
        /// <param name="rowIndex"></param>
        private static void CreateTitle(this ISheet sheet1, string title, int lastCol, int rowIndex, ICellStyle style = null)
        {
            sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, lastCol));
            IRow row = sheet1.CreateRow(rowIndex);
            row.Height = 30 * 20;
            var cell = row.CreateCell(0);
            if (style != null) cell.CellStyle = style;
            cell.SetCellValue(title);
            sheet1.AutoSizeColumn(0);
        }

        /// <summary>
        /// 创建内容标题
        /// </summary>
        /// <param name="sheet1"></param>
        /// <param name="colNames"></param>
        /// <param name="rowIndex"></param>
        private static void CreateSheetTitle(this ISheet sheet1, List<string> colNames, int rowIndex, bool isSeq, ICellStyle style = null)
        {
            sheet1.SetColumnWidth(rowIndex, 10 * 256);

            IRow row = sheet1.CreateRow(rowIndex);
            var index = 0;

            if (isSeq) colNames.Insert(0, "序号");

            foreach (var item in colNames)
            {
                var cell = row.CreateCell(index);
                if (style != null) cell.CellStyle = style;
                cell.SetCellValue(item);
                index++;
            }
        }

        /// <summary>
        /// 创建内容
        /// </summary>
        /// <param name="sheet1"></param>
        /// <param name="list"></param>
        /// <param name="rowIndex"></param>
        private static void CreateSheetRow(this ISheet sheet1, Dictionary<string, string> list, int rowIndex, int colIndex, bool isSeq, ICellStyle style = null)
        {
            sheet1.SetColumnWidth(rowIndex, 10 * 256);

            IRow row = sheet1.CreateRow(rowIndex);
            var index = 0;

            if (isSeq)
            {
                var cell = row.CreateCell(index);
                if (style != null) cell.CellStyle = style;
                cell.SetCellValue($"{colIndex}");
                index++;
            }

            foreach (var item in list)
            {
                var cell = row.CreateCell(index);
                if (style != null) cell.CellStyle = style;
                cell.SetCellValue(item.Value);
                index++;
            }
        }
    }
}
